from . import db
from sqlalchemy.types import JSON
from uuid import uuid4
from sqlalchemy import func, DateTime
from .new_models import (
    CareElderDemand,
    CareGbNursingAction,
    DigitalEmployeeSkills,
    AppUser,
    AppUserDetail,
    CareScheduleResultOld,
    CareTimeSlotOld,
)


# 使用新表替代原表
# 原表已被删除，这里使用新表作为替代
# 为了保持代码兼容性，我们创建别名

# elder_demand -> care_elder_demand
Elderdemand = CareElderDemand

# attendant_skills -> digital_employee_skills
class Attendantskills:
    """
    数字员工技能表的兼容层

    由于原表结构与新表结构有差异，这里提供一个兼容层
    """
    @staticmethod
    def query():
        """模拟SQLAlchemy查询接口"""
        class AttendantskillsQuery:
            @staticmethod
            def all():
                """
                获取所有护理员技能

                将DigitalEmployeeSkills表的数据转换为Attendantskills格式
                """
                skills = DigitalEmployeeSkills.query.all()
                result = []
                for skill in skills:
                    # 创建一个类似Attendantskills的对象
                    attendant = type('AttendantskillsObj', (), {
                        'id': str(skill.id),
                        'attendant_name': skill.name,
                        'skills': str(skill.id),  # 使用ID作为技能
                    })
                    result.append(attendant)
                return result

            @staticmethod
            def filter_by(**kwargs):
                """模拟filter_by方法"""
                return AttendantskillsQuery()

            @staticmethod
            def first():
                """获取第一条记录"""
                skills = DigitalEmployeeSkills.query.first()
                if skills:
                    return type('AttendantskillsObj', (), {
                        'id': str(skills.id),
                        'attendant_name': skills.name,
                        'skills': str(skills.id),
                    })
                return None

        return AttendantskillsQuery()


# dic_nursing_actions -> care_gb_nursing_action
DicNursingAction = CareGbNursingAction

# schedule_results -> care_schedule_results_old
ScheduleResult = CareScheduleResultOld

# time_slots -> care_time_slots_old
TimeSlot = CareTimeSlotOld


class CaregiverSchedule(db.Model):
    """护理员排班表"""
    __tablename__ = "caregiver_schedules"
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    caregiver_id = db.Column(db.String(36), nullable=False)
    schedule_date = db.Column(db.DateTime, nullable=False)
    time_slot = db.Column(db.Integer, nullable=False)
    is_available = db.Column(db.Boolean, default=True)

    def __repr__(self):
        return f"<CaregiverSchedule {self.caregiver_id} {self.schedule_date} {self.time_slot}>"


class ShiftAssignment(db.Model):
    """班次分配表"""
    __tablename__ = "shift_assignments"
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    caregiver_id = db.Column(db.String(255), nullable=False)
    caregiver_name = db.Column(db.String(255), nullable=False)
    shift_date = db.Column(db.Date, nullable=False)
    shift_type = db.Column(db.String(20), nullable=False)  # 'morning', 'afternoon', 'night'
    shift_start_time = db.Column(db.Time, nullable=False)
    shift_end_time = db.Column(db.Time, nullable=False)
    schedule_type = db.Column(db.String(20), nullable=False, default="daily")  # 'daily', 'weekly', 'monthly', 'quarterly', 'yearly'
    created_at = db.Column(db.DateTime, nullable=False, default=func.now())
    updated_at = db.Column(db.DateTime, nullable=False, default=func.now(), onupdate=func.now())  # 更新时间字段

    def __repr__(self):
        return f"<ShiftAssignment {self.id} {self.caregiver_name} {self.shift_date} {self.shift_type}>"

    @classmethod
    def get_assignments_by_date_range(cls, start_date, end_date, date_desc=True):
        # 使用自定义排序逻辑，根据班次名称中的数字进行排序
        from sqlalchemy import case, desc

        # 定义班次排序规则
        shift_order = case(
            [
                (cls.shift_type == "班次1", 1),
                (cls.shift_type == "班次2", 2),
                (cls.shift_type == "班次3", 3),
            ],
            else_=4,
        )

        query = cls.query.filter(
            cls.shift_date >= start_date, cls.shift_date <= end_date
        )

        # 根据参数决定日期是升序还是降序
        if date_desc:
            return query.order_by(desc(cls.shift_date), shift_order).all()
        else:
            return query.order_by(cls.shift_date, shift_order).all()

    @classmethod
    def get_assignments_by_caregiver(
        cls, caregiver_id, start_date, end_date, date_desc=True
    ):
        # 使用自定义排序逻辑，根据班次名称中的数字进行排序
        from sqlalchemy import case, desc

        # 定义班次排序规则
        shift_order = case(
            [
                (cls.shift_type == "班次1", 1),
                (cls.shift_type == "班次2", 2),
                (cls.shift_type == "班次3", 3),
            ],
            else_=4,
        )

        query = cls.query.filter(
            cls.caregiver_id == caregiver_id,
            cls.shift_date >= start_date,
            cls.shift_date <= end_date,
        )

        # 根据参数决定日期是升序还是降序
        if date_desc:
            return query.order_by(desc(cls.shift_date), shift_order).all()
        else:
            return query.order_by(cls.shift_date, shift_order).all()
